package com.qhm123.spark.model;

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DbHelper {

	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/boooo?useUnicode=true&characterEncoding=utf8";
	private static final String DB_USER = "root";
	private static final String DB_PASSWORD = "qhm123";
	private static DbHelper helper;
	private Connection conn;

	public static DbHelper getInstance() {
		if (helper == null) {
			helper = new DbHelper();
		}

		return helper;
	}

	public void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private DbHelper() {
		try {
			Class.forName(DB_DRIVER);
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			if (!conn.isClosed()) {
				System.out.println("Succeeded connecting to the Database!");
			}
		} catch (ClassNotFoundException e) {
			System.out.println("Sorry,can`t find the Driver!");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static String convertToHex(byte[] data) {
		StringBuffer buf = new StringBuffer();
		for (int i = 0; i < data.length; i++) {
			int halfbyte = (data[i] >>> 4) & 0x0F;
			int two_halfs = 0;
			do {
				if ((0 <= halfbyte) && (halfbyte <= 9))
					buf.append((char) ('0' + halfbyte));
				else
					buf.append((char) ('a' + (halfbyte - 10)));
				halfbyte = data[i] & 0x0F;
			} while (two_halfs++ < 1);
		}
		return buf.toString();
	}

	public static String toMD5(String value) {
		byte[] bytesOfMessage;
		String valueMD5 = "";
		try {
			bytesOfMessage = value.getBytes("UTF-8");
			MessageDigest digest = MessageDigest.getInstance("MD5");
			digest.update(bytesOfMessage);
			byte[] hash = digest.digest();
			valueMD5 = convertToHex(hash);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		}
		return valueMD5;
	}

	public void putBook(Book book) {
		String sql = String
				.format("INSERT INTO book (id, title, author, summary,"
						+ " image_url, isbn, translator, publisher, pubdate, price) "
						+ "values (\"%s\", \"%s\", \"%s\", \"%s\","
						+ " \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\")",
						book.id, book.title, book.author, book.summary,
						book.imageurl, book.isbn, book.translator,
						book.publisher, book.pubdate, book.price);
		executeUpdate(sql);
	}

	public int putTag(String value) {
		String valueMD5 = toMD5(value);
		try {
			Statement st = conn.createStatement();
			String sql = String.format(
					"SELECT * FROM tag WHERE value_md5 = \"%s\"", valueMD5);
			ResultSet rs = st.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		String sql = String.format(
				"INSERT INTO tag (value, value_md5) values (\"%s\", \"%s\")",
				value, valueMD5);
		return executeUpdateWithReturn(sql);
	}

	public List<Book> getBooks() {
		List<Book> books = new ArrayList<Book>();
		try {
			Statement st = conn.createStatement();
			String sql = "SELECT * FROM book";
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				Book book = new Book();
				book.id = rs.getString("id");
				book.author = rs.getString("author");
				book.summary = rs.getString("summary");
				book.title = rs.getString("title");
				books.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return books;
	}

	public void putUser(User user) {
		String sql = String.format("INSERT INTO user (id, name, password) "
				+ "values (\"%s\", \"%s\", \"%s\")", user.id, user.name,
				user.password);
		executeUpdate(sql);
	}

	public void putRating(String userId, String bookId, float value) {
		String sql = String.format("INSERT INTO preferences "
				+ "(book_id, user_id, preference)"
				+ " values (\"%s\", \"%s\", \"%s\")", bookId, userId, value);
		executeUpdate(sql);
	}

	public void putBookTag(String bookId, String tagId) {
		String sql = String.format("INSERT INTO book_tag (book_id, tag_id) "
				+ "values (\"%s\", \"%s\")", bookId, tagId);
		executeUpdate(sql);
	}

	private int executeUpdateWithReturn(String sql) {
		Statement statement;
		int id = -1;
		try {
			statement = conn.createStatement();
			statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			id = rs.getInt(1);
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return id;
	}

	private void executeUpdate(String sql) {
		Statement statement;
		try {
			statement = conn.createStatement();
			statement.executeUpdate(sql);
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String arg[]) {
		DbHelper helper = DbHelper.getInstance();

		helper.close();
	}
}
